USE Partitioning;
GO

-- get date range
SELECT
	MIN(orderdate) AS min_date,
	MAX(orderdate) AS max_date
FROM dbo.Orders;
-- 2007-12-02 00:00:00.000, 2013-12-31 00:00:00.000

-- run the following query and display the showplan
SET STATISTICS XML ON;
GO

SELECT * FROM dbo.Orders
WHERE orderdate >= '20100101';

SET STATISTICS XML OFF;
GO

-- in the xml representation you should see something similar to this
/*
<Object Database="[Partitioning]" Schema="[dbo]" Table="[Orders]" Index="[idx_cl_od]" IndexKind="Clustered" />
<SeekPredicates>
  <SeekPredicateNew>
    <SeekKeys>
      <StartRange ScanType="GE">
        <RangeColumns>
          <ColumnReference Database="[Partitioning]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
        </RangeColumns>
        <RangeExpressions>
          <ScalarOperator ScalarString="'2010-01-01 00:00:00.000'">
            <Const ConstValue="'2010-01-01 00:00:00.000'" />
          </ScalarOperator>
        </RangeExpressions>
      </StartRange>
    </SeekKeys>
  </SeekPredicateNew>
</SeekPredicates>
*/

-- inspect the database layout in SQL MGT Studio and Windows Explorer (C:\MSSQL\Data)

-- add filegroup
ALTER DATABASE Partitioning ADD FILEGROUP [SECONDARY];
GO

-- create file for this filegroup
ALTER DATABASE Partitioning ADD FILE (NAME = Partitioning_Archive,
FILENAME = 'C:\MSSQL\Data\Secondary\Partitioning.ndf', SIZE = 246MB, FILEGROWTH = 128MB)
TO FILEGROUP [SECONDARY];
GO

-- inspect the database layout in SQL MGT Studio and Windows Explorer (C:\MSSQL\Data)

-- verify filegroups
SELECT * FROM sys.filegroups;
GO